package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import control.ConnectionManage;
import db.parking.beans.Orders;

/**
 * AdminCanceledOrders manages all requests From,To table canceledorders
 * @author Operations
 *
 */
public class AdminCanceledOrders {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	
	/****
	 * getOrdersCountPerDay**
	 * 
	 * @param theStartDay
	 * @return int  count of order per day
	 */
	@SuppressWarnings("deprecation")
	public static int canceldOrdersCount(Timestamp startDay,Timestamp endDay, int lotNo)		//get rum of canceled orders during the day
			throws SQLException {
		int numOfOrders;
		String sql = "SELECT * FROM canceledorders INNER JOIN orders ON canceledorders.oid= orders.oid WHERE canceledorders.canceledtime >= ? AND canceledorders.canceledtime < ? AND orders.lotNo=?"; 
		
		
		// calc the start of the day like 2014-02-26 00:00:00 000
		
		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
		

		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			stmt.setInt(3, lotNo);
			rs = stmt.executeQuery();
			rs.last();
			numOfOrders = rs.getRow();
			return numOfOrders;
		} catch (SQLException e) {
			System.err.println(e+ "AdminCanceldOrdersCount->canceldOrdersCount");
			return 0;
		}
	}
	
	
	/**
	 * Functins inserts an Order bean enitity into database table cancelOrders.
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insert(Orders bean) throws Exception {
		//insert new canceled order
		// complaintID is autoIncremented on Insert
		String sql = "INSERT into canceledorders (oid , canceledtime) "
				+ "VALUES (?, ?)";
		ResultSet keys = null;
		long time= System.currentTimeMillis();
		Timestamp st=new Timestamp(time);
		
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			stmt.setInt(1, bean.getOid());
			stmt.setTimestamp(2, st);
						
			int affected = stmt.executeUpdate();

			if (affected == 1) {
				return true;
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e ) {
			System.err.println(e + "  AdminCancelOrder");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
	}
}
